﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Reflection;
using System.Text;
using MySql.Data.MySqlClient;
using UnityEngine;

namespace Utils
{
	public static class MySQLUtil
	{
		private static MySqlConnection dbConnection;

		#region getter和setter
		public static string Host { get; set; } = "localhost";
		public static string Port { get; set; } = "3306";
		public static string Username { get; set; } = "root";
		public static string Password { get; set; } = "root";
		public static string Database { get; set; } = "card_game";

		#endregion

		public static void CreateConnection()
		{
			if(dbConnection!=null && dbConnection.State.Equals(ConnectionState.Open))
			{
				Debug.Log("连接已经创建，不再创建新的连接");
				return;
			}
			//连接数据库
			try
			{
				string connectionString =$"server = {Host};port={Port};database = {Database};user = {Username};password = {Password};";
				Debug.Log("当前正在进行的连接信息为："+connectionString);
				dbConnection = new MySqlConnection(connectionString);
				dbConnection.Open();
				Debug.Log("连接成功！");
			}
			catch (Exception e)
			{
				throw new Exception("连接失败！" + e.Message.ToString());
			}
		}

		//关闭连接
		public static void Close()
		{
			if (dbConnection is { State: ConnectionState.Open })
			{
				dbConnection.Close();
				dbConnection.Dispose();
				dbConnection = null;
			}
			else
			{
				Debug.Log("连接已经关闭，请不要重复关闭连接");
			}
		}

		/// <summary>
		/// 根据指定条件查询指定表并将结果封装到泛型类型的列表中
		/// </summary>
		/// <typeparam name="T">泛型类型，需要有默认构造函数</typeparam>
		/// <param name="tableName">表名</param>
		/// <param name="columns">返回的字段字符串</param>
		/// <param name="condition">查询条件</param>
		/// <param name="start">分页起始下表</param>
		/// <param name="end">分页结束下表</param>
		/// <returns>封装查询结果的泛型类型列表</returns>
		public static List<T> SelectData<T>(string tableName, string columns, string condition , int start , int end) where T : new()
		{
			StringBuilder query = new StringBuilder();
			query.Append("SELECT ");
			query.Append(columns);

			query.Append(" FROM ");
			query.Append(tableName);

			if (condition != null && !condition.Trim().Equals(""))
			{
				query.Append(" WHERE ");
				query.Append(condition);
			}

			if (start != -1 && end != -1)
			{
				query.Append($" LIMIT {start} , {end}");
			}

			DataSet dataSet = ExecuteSQL(query.ToString());
			return ConvertResult<T>(dataSet);
		}
		
		/// <summary>
		/// 根据指定条件查询指定表并将结果封装到泛型类型的列表中
		/// </summary>
		/// <typeparam name="T">泛型类型，需要有默认构造函数</typeparam>
		/// <param name="tableName">表名</param>
		/// <param name="columns">返回的字段字符串</param>
		/// <param name="condition">查询条件</param>
		/// <returns>封装查询结果的泛型类型列表</returns>
		public static List<T> SelectData<T>(string tableName, string columns, string condition) where T : new()
		{
			return SelectData<T>(tableName, columns, condition, -1 ,-1);
		}
		
		public static List<T> SelectData<T>(string sql) where T : new()
		{
			DataSet dataSet = ExecuteSQL(sql);
			return ConvertResult<T>(dataSet);
		}

		public static void UpdateData(string sql)
		{
			ExecuteSQL(sql);
		}
		
		public static void UpdateData<T>(string tableName, string keyName , long id, T data)
		{
			// 获取数据类型的属性信息
			PropertyInfo[] properties = typeof(T).GetProperties();
			StringBuilder setClause = new StringBuilder();

			// 遍历属性，筛选出非空且非默认值的属性
			foreach (PropertyInfo property in properties)
			{
				object value = property.GetValue(data);
				if (IsValueValid(value))
				{
					if (setClause.Length > 0)
					{
						setClause.Append(", ");
					}
					setClause.Append($"{property.Name} = ");
					if (value is string || value is DateTime)
					{
						// 字符串和日期类型需要用单引号包裹，同时转义单引号
						setClause.Append($"'{value.ToString().Replace("'", "''")}'");
					}
					else if (value is bool)
					{
						// 布尔类型转换为 MySQL 中的 0 或 1
						setClause.Append((bool)value ? 1 : 0);
					}
					else
					{
						setClause.Append(value.ToString());
					}
				}
			}

			// 如果没有有效字段需要更新，直接返回
			if (setClause.Length == 0)
			{
				return;
			}

			// 构建完整的 SQL 更新语句
			string sql = $"UPDATE {tableName} SET {setClause} WHERE {keyName} = {id}";
			Debug.Log("当前正在执行的更新语句：" + sql);
			ExecuteSQL(sql);
		}

		// 判断属性值是否有效（非空且非默认值）
		private static bool IsValueValid(object value)
		{
			if (value == null)
			{
				return false;
			}

			// 处理值类型默认值
			if (value.GetType().IsValueType)
			{
				return !value.Equals(Activator.CreateInstance(value.GetType()));
			}

			// 处理字符串空值
			if (value is string strValue)
			{
				return !string.IsNullOrEmpty(strValue);
			}

			return true;
		}
		
		public static void AddData(string sql)
		{
			ExecuteSQL(sql);
		}

		public static long AddData<T>(T data, string tableName)
		{
			StringBuilder sql = new StringBuilder($"insert into {tableName} (");
			string columnStr = GetColumnString<T>(data);
			string valueStr = GetValueString<T>(data);
			sql.Append($"{columnStr}) values ({valueStr});");
			string sqlQuery = sql.ToString();
			Debug.Log("当前正在执行的插入语句：" + sqlQuery);
			ExecuteSQL(sqlQuery);
	
			// 获取最后插入的自增ID
			if (dbConnection != null && dbConnection.State == ConnectionState.Open)
			{
				MySqlCommand cmd = new MySqlCommand("SELECT LAST_INSERT_ID();", dbConnection);
				try
				{
					object result = cmd.ExecuteScalar();
					if (result != null && result != DBNull.Value)
					{
						return Convert.ToInt64(result);
					}
				}
				catch (MySqlException ex)
				{
					Debug.LogError($"获取最后插入ID时发生 MySQL 异常: {ex.Message}");
				}
				catch (Exception ex)
				{
					Debug.LogError($"获取最后插入ID时发生未知异常: {ex.Message}");
				}
			
			}
			return 0;
		}

		// 根据实体类的类型获取字段字符串
		private static string GetColumnString<T>(T data)
		{
			System.Reflection.PropertyInfo[] properties = typeof(T).GetProperties();
			StringBuilder columnBuilder = new StringBuilder();
	
			for (int i = 0; i < properties.Length; i++)
			{
				if (i > 0)
				{
					columnBuilder.Append(", ");
				}
				columnBuilder.Append(properties[i].Name);
			}
	
			return columnBuilder.ToString();
		}

		// 根据实体类的类型获取属性值字符串
		private static string GetValueString<T>(T data)
		{
			System.Reflection.PropertyInfo[] properties = typeof(T).GetProperties();
			StringBuilder valueBuilder = new StringBuilder();
	
			for (int i = 0; i < properties.Length; i++)
			{
				if (i > 0)
				{
					valueBuilder.Append(", ");
				}
				object value = properties[i].GetValue(data);
				if (value == null)
				{
					valueBuilder.Append("NULL");
				}
				else if (value is string || value is DateTime)
				{
					// 字符串和日期类型需要用单引号包裹
					valueBuilder.Append($"'{value.ToString().Replace("'", "''")}'");
				}
				else if (value is bool)
				{
					// 布尔类型转换为 MySQL 中的 0 或 1
					valueBuilder.Append((bool)value ? 1 : 0);
				}
				else
				{
					valueBuilder.Append(value.ToString());
				}
			}

			return valueBuilder.ToString();
		}

		// 执行sql语句
		private static DataSet ExecuteSQL(string sqlString)
		{
			Debug.Log("当前正在执行的SQL语句：" + sqlString);	
			DataSet dataSet = new DataSet();
			if (dbConnection == null || dbConnection.State != ConnectionState.Open)
			{
				Debug.LogError("数据库连接未打开，请先调用 CreateConnection 方法。");
				return dataSet;
			}

			try
			{
				MySqlDataAdapter adapter = new MySqlDataAdapter(sqlString, dbConnection);
				// 使用 MySqlDataAdapter 填充 DataSet
				adapter.Fill(dataSet);
			}
			catch (MySqlException ex)
			{
				Debug.LogError($"执行 SQL 语句时发生 MySQL 异常: {ex.Message}");
			}
			catch (Exception ex)
			{
				Debug.LogError($"执行 SQL 语句时发生未知异常: {ex.Message}");
			}

			return dataSet;
		}

		private static List<T> ConvertResult<T>(DataSet dataSet) where T : new()
		{
			List<T> resultList = new List<T>();

			if (dataSet.Tables.Count > 0 && dataSet.Tables[0].Rows.Count > 0)
			{
				System.Reflection.PropertyInfo[] properties = typeof(T).GetProperties();
				foreach (DataRow row in dataSet.Tables[0].Rows)
				{
					T item = new T();
					foreach (System.Reflection.PropertyInfo property in properties)
					{
						if (dataSet.Tables[0].Columns.Contains(property.Name))
						{
							object value = row[property.Name];
							if (value != DBNull.Value)
							{
								property.SetValue(item, Convert.ChangeType(value, property.PropertyType), null);
							}
						}
					}
					resultList.Add(item);
				}
			}

			return resultList;
		}
	}
}
